package service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import net.sf.json.JSONArray;
import utils.DB;
import vo.User;;

public class UserService {
	
	public int addUser(User user) {
		Connection conn = null;
		PreparedStatement stmt = null;
		
		try {
			conn = DB.getConn();
			String sql = "insert into user(username,password) values(?,?)";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, user.getUsername());
			stmt.setString(2, user.getPassword());
			
			int result = stmt.executeUpdate();
			System.out.println(sql);
			return result;
		}catch(Exception e) {
			e.printStackTrace();
			return -1;
		}finally {
			try {
				if(stmt!=null)
					stmt.close();
				if(conn!=null)
					conn.close();
			}catch(Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	public int updateUser(User user) {
		Connection conn = null;
		PreparedStatement stmt = null;
		
		try {
			conn = DB.getConn();
//			update t_test t set t.password = '*', t.remark = '*' where t.bs = 1;
			String sql = "update user u set u.username=?,u.password=? where u.id=?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, user.getUsername());
			stmt.setString(2, user.getPassword());
			stmt.setInt(3, user.getId());
			
			int result = stmt.executeUpdate();
			System.out.println(sql+"....."+result+"..."+user.getUsername()+"..."+user.getId());
			return result;
		}catch(Exception e) {
			e.printStackTrace();
			return -1;
		}finally {
			try {
				if(stmt!=null)
					stmt.close();
				if(conn!=null)
					conn.close();
			}catch(Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	public int delUser(int id) {
		Connection conn = null;
		PreparedStatement stmt = null;
		
		try {
			conn = DB.getConn();
			String sql = "delete from user where id=?";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, id);
			
			int result = stmt.executeUpdate();
			return result;
		}catch(Exception e) {
			e.printStackTrace();
			return -1;
		}finally {
			try {
				if(stmt!=null)
					stmt.close();
				if(conn!=null)
					conn.close();
			}catch(Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	public String getUsers() {
		List<User> users = new ArrayList<User>();


		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		try {
			conn = DB.getConn();
			stmt = conn.createStatement();
			
			String sql = "select * from user";
			rs = stmt.executeQuery(sql);
			
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				users.add(user);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if(rs!=null)
					rs.close();
				if(stmt!=null)
					stmt.close();
				if(conn!=null)
					conn.close();
			}catch(Exception e) {
				e.printStackTrace();
			}
		}
		
		return JSONArray.fromObject(users).toString();
	}
}
